﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace WindowsFormsApplication1
{
        class mydatabase
        {
            public SqlConnection connection;
            public void open()
            {
                string connstring = "server=DESKTOP-PCB2O8Q; database=达标训练1; Integrated Security=True";
                connection = new SqlConnection(connstring);
                connection.Open();
            }
            public void close()
            {
                connection.Dispose();
                connection.Close();
                connection = null;
            }
            public SqlDataReader getdatareader(string sqlstring)
            {
                open();
                SqlCommand mycom = new SqlCommand(sqlstring, connection);
                SqlDataReader dr = mycom.ExecuteReader();
                return dr;
            }
            public DataSet getdataset(string sqlstring)
            {
                open();
                SqlCommand mycom = new SqlCommand(sqlstring, connection);
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = mycom;
                DataSet dataset = new DataSet();
                adapter.Fill(dataset);
                close();
                return dataset;
            }
            public int excutesql(string sqlstring)
            {
                int count = -1;
                open();
                try
                {
                    SqlCommand cmd = new SqlCommand(sqlstring, connection);
                    count = cmd.ExecuteNonQuery();
                }
                catch
                {
                    count = -1;
                }
                finally
                {
                    close();
                }
                return count;
            }
            public int getdatarow(string sqlstring)
            {
                int countrow = 0;
                open();
                SqlCommand mycom = new SqlCommand(sqlstring, connection);
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = mycom;
                DataSet ds = new DataSet();
                da.Fill(ds);
                ds.CaseSensitive = false;
                countrow = ds.Tables[0].Rows.Count;
                close();
                return countrow;
            }
            public DataTable getdatabale(string sqlstring)
            {
                DataSet ds = getdataset(sqlstring);
                DataTable dt = new DataTable();
                dt = ds.Tables[0];
                return dt;
            }
            public object getscalar(string sqlstring)
            {
                open();
                SqlCommand mycom = new SqlCommand(sqlstring, connection);
                object result = mycom.ExecuteScalar();
                close();
                return result;
            }
            public bool doupdate(DataSet ds, string sql, string tablename)
            {
                bool flag = false;
                open();
                using (SqlDataAdapter da = new SqlDataAdapter(sql, connection))
                {
                    SqlCommandBuilder builder = new SqlCommandBuilder(da);
                    try
                    {
                        lock (this)
                        {
                            da.Update(ds, tablename);
                            flag = true;
                        }
                    }
                    catch (SqlException e)
                    {
                        throw new Exception(e.Message);
                    }
                }
                close();
                return flag;
            }
            public ArrayList getlistarray(string sqlstring, int m)
            {
                ArrayList array = new ArrayList();
                SqlDataReader dr = getdatareader(sqlstring);
                while (dr.Read())
                {
                    array.Add(dr.GetValue(m));
                }
                return array;
            }
            public SqlCommand getproccommand(string sqlstring)
            {
                open();
                SqlCommand mycommand = new SqlCommand(sqlstring, connection);
                return mycommand;
            }
        }
    }

